var mysql = require('mysql');
const JSON5 = require('json5')
const fs = require('fs')
const path = require('path')
const jwt = require('jsonwebtoken')
function getJsonFile(filePath) {
  // 读取指定json文件
  var json = fs.readFileSync(path.resolve(__dirname, filePath), 'utf-8');
  // 解析并返回
  return JSON5.parse(json);
}
var EXPIRESD = 60 * 60 * 24;
var hostaddr = "http://chengyubbs.51vip.biz"
var PRIVITE_KEY = 'secret';
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'rjgc',
  multipleStatements: true
});
connection.connect();
function Mysql() {
  this.findoneuser = function (peo, res) {
    var sql1 = 'SELECT * FROM user where (userName = ? and Password = ?)';
    var modSqlParams = [peo.username, peo.password];
    connection.query(sql1, modSqlParams, function (err, result) {

      if (err) {
        return;
      }
      if (result[0]) {
        var returnlogin = getJsonFile('./loginreturn');
        returnlogin.data.userid = result[0].userID;
        var y = { name: result[0].userID };
        //console.log(y);
        var token = jwt.sign(y, PRIVITE_KEY, { expiresIn: EXPIRESD });
        // console.log("token:");
        // console.log(token);
        returnlogin.data.token = token;
        res.json(returnlogin);
      }
      // console.log(result[0].userID); 
    });
  }
  this.findalluser = function () {
    var sql1 = 'SELECT * FROM user';
    connection.query(sql1, function (err, result) {
      if (err) {
        console.log('[SELECT ERROR] - ', err.message);
        return;
      }
      console.log(result);
    });
  }
  function addoneuser(peo, res) {
    var sql1 = 'INSERT INTO user(userName,Password) VALUES(?,?)';
    var modSqlParams1 = [peo.username, peo.password];
    connection.query(sql1, modSqlParams1, function (err, result) {
      if (err) {
        return;
      }
      var returndata = getJsonFile('./users.json5');
      returndata.data.userid = result.insertId;
      console.log(returndata);
      res.json(returndata);
    });

  }
  this.adduser = function (peo, res) {
    var sql2 = 'SELECT * FROM user where userName = ?';
    var modSqlParams2 = [peo.username];
    connection.query(sql2, modSqlParams2, function (err, result) {
      if (err) {
        return;
      }
      if (result[0]) {

      }
      else {
        addoneuser(peo, res);
      }
    });

  }
  //对比时间先后
  function check(t1, t2, t3, t4, t5, t6, y1, y2, y3, y4, y5, y6) {
    //console.log(t1, y1);
    if (t1 > y1) return 1;
    if (t1 < y1) return 0;
    //console.log(t2, y2);
    if (t2 > y2) return 1;
    if (t2 < y2) return 0;
    //console.log(t3, y3);
    if (t3 > y3) return 1;
    if (t3 < y3) return 0;
    //console.log(t4, y4);
    if (t4 > y4) return 1;
    if (t4 < y4) return 0;
    //console.log(t5, y5);
    if (t5 > y5) return 1;
    if (t5 < y5) return 0;
    //console.log(t6, y6);
    if (t6 > y6) return 1;
    if (t6 < y6) return 0;
    return 1;
  }
  //查询问卷
  this.findquestionnaire = function (peo, pagenum, pagesize, typ, res) {
    //console.log(pagenum, pagesize, "sql OK");
    var date = new Date();
    var year = date.getFullYear();
    var month = date.getMonth() + 1;
    var day = date.getDate();
    var hour = date.getHours();
    var minute = date.getMinutes();
    var second = date.getSeconds();
    // console.log(year,month,day,hour,minute,second);
    var sql = "SELECT * FROM questionnaire where CH = ? and userID = ? ";
    var ch = '1'
    if (typ == 1) ch = '0';
    else ch = '1';
    var modSqlParams = [ch, peo.name];


    connection.query(sql, modSqlParams, function (err, result) {
      if (err) {
        console.log(err);
        return;
      }
      var json = getJsonFile('./questionnaire.json5');
      json.data.totalpage = result.length;
      var limit = result.length;
      json.data.questionnaires = [];
      var st = (pagenum - 1) * pagesize;
      var en = pagenum * pagesize;
      if (en > limit) {
        en = result.length;
      }
      console.log("st: ", st, "pagesize: ", pagesize, "en: ", en);
      for (var i = st; i < en; ++i) {
        console.log(result[i].title);
        var t = result[i].addtime;
        var mydate = new Date(t);
        var myear = mydate.getFullYear();
        var mmonth = mydate.getMonth() + 1;
        var mday = mydate.getDate();
        var mhour = mydate.getHours();
        var mminute = mydate.getMinutes();
        var msecond = mydate.getSeconds();
        var tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
        result[i].addtime = tmp;

        t = result[i].releasetime;
        //为空则一定是未发布
        if (t == null)
          result[i].status = "未发布";
        else {
          mydate = new Date(t);
          myear = mydate.getFullYear();
          mmonth = mydate.getMonth() + 1;
          mday = mydate.getDate();
          mhour = mydate.getHours();
          mminute = mydate.getMinutes();
          msecond = mydate.getSeconds();
          if (check(year, month, day, hour, minute, second, myear, mmonth, mday, mhour, mminute, msecond) == 1) {
            result[i].status = "发布中";
          }
          else result[i].status = "未发布";
          tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
          result[i].releasetime = tmp;

          t = result[i].endtime;
          mydate = new Date(t);
          myear = mydate.getFullYear();
          mmonth = mydate.getMonth() + 1;
          mday = mydate.getDate();
          mhour = mydate.getHours();
          mminute = mydate.getMinutes();
          msecond = mydate.getSeconds();
          if (check(year, month, day, hour, minute, second, myear, mmonth, mday, mhour, mminute, msecond) == 1) {
            result[i].status = "已结束";
          }
          tmp = myear + '-' + mmonth + '-' + mday + ' ' + mhour + ':' + mminute + ':' + msecond;
          result[i].endtime = tmp;
          var gg = result[i].questionnaireid;
          result[i].questionnaireid = gg.toString();
        }
        var rrr = result[i]
        rrr.link = hostaddr + "/fillin?questionnaireid=" + result[i].questionnaireid;
        json.data.questionnaires.push(rrr);
      }
      res.json(json);

    });
  }
  //删除问卷
  this.delquestionnaire = function (idstodel, type, res) {
    var json = getJsonFile('./deleteQuestionnaire.json5');
    if (idstodel == null) {
      json.meta.msg = 'id为空';
      json.meta.status = 400;
      res.json(json)
      return;
    }
    var nbrOfId = idstodel.length;

    //先逐个查询
    var querySql = 'SELECT * FROM questionnaire where questionnaireid = ? ';
    for (var i = 0; i < nbrOfId; ++i) {
      var modSqlParams = [idstodel[i]];
      connection.query(querySql, modSqlParams, function (err, result) {
        if (err) {
          console.log(err);
          json.meta.msg = '未查询到所有问卷信息';
          res.json(json)
          return;
        }
      })
    }
    //所有问卷都已找到，再开始删除
    delSql = "UPDATE questionnaire SET CH=? where questionnaireid = ? ";
    var chval
    console.log('delete type: ', type)
    if (type == 1)
      chval = '1'
    else
      chval = '2'
    console.log('chval: ', chval)
    for (var i = 0; i < nbrOfId; ++i) {
      var modSqlParams = [chval, idstodel[i]];
      connection.query(delSql, modSqlParams, function (err, result) {
        if (err) {
          console.log(err);
          json.meta.msg = '删除失败';
          res.json(json)
          return;
        }
      })
    }
    res.json(json);

  }
  this.addexam = function (id, res) {
    //console.log("in add exam");
    var sql = "insert into questionnaire (addtime,userID,CH) VALUES(?,?,?)";
    var time = new Date();
    var para = [time, id, "0"];
    connection.query(sql, para, function (err, result) {
      var json = getJsonFile('./add.json5');
      if (err) {
        console.log("err in add: ", err);
        json.meta.status = 400;
        res.json(json)
        return;
      }
      json.data.questionnaireid = result.insertId;
      console.log("new questionnaire id: ", result.insertId);
      res.json(json);
    })
  }

  function clearexam(id, res) {
    var sql = 'delete from question where questionnaireid=?';
    var para = [id];
    connection.query(sql, para, function (err, result) {
      var json = getJsonFile('./save.json5');
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json);
        return 0;
      }

    })
    console.log("cleared id:", id);
    return 1;
  }
  function addoption(selects, id, res) {
    console.log("selects length: ", selects.length);
    if (selects.length == 0) {
      return 1;
    }

    console.log("addoption start");
    var sql3 = 'insert into options (questionID,selects) values(?,?)';
    var tp = '';
    for (var j = 0; j < selects.length; ++j) {
      tp = tp + selects[j];
      if (j + 1 < selects.length) {
        //采用 | 作为分隔符
        tp = tp + '|';
      }
    }
    var para3 = [id, tp];
    console.log("tp: ", id, tp);
    connection.query(sql3, para3, function (err, result) {
      var json = getJsonFile('./save.json5');
      if (err) {
        json.meta.status = 400;
        console.log("errop: ", err);
        res.json(json);
        return 0;
      }
    })
    console.log("addoption end");
    return 1;
  }
  function addquestion(ques, id, res) {
    var re = [];
    for (let i = 0; i < ques.length; ++i) {
      var sql2 = 'insert into question (title,topic,questionnaireid,sequenceID) values(?,?,?,?)'
      var para2 = [ques[i].title, ques[i].topic, id, i + 1];
      console.log("ques: ", ques[i], ques[i].selects);
      var jk = ques[i].selects;
      connection.query(sql2, para2, function (err, result) {
        var json = getJsonFile('./save.json5');
        if (err) {
          console.log(err);
          json.meta.status = 400;
          res.json(json);
          return 0;
        }
        console.log("jk: ", i, ques[i].selects);
        var kk = addoption(ques[i].selects, result.insertId, res);
        if (kk == 0) {
          return 0;
        }
      })
    }

    return 1;
  }

  this.saveexam = function (rev, res) {
    console.log("mysql save");
    var sql1 = 'update questionnaire set title=?,endtime=?,introduction=? where questionnaireid=?'
    var d = new Date(rev.endtime * 1000);
    var para1 = [rev.title, d, rev.introduction, rev.questionnaireid];
    console.log(rev)
    connection.query(sql1, para1, function (err, result) {
      var json = getJsonFile('./save.json5');
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json);
        return;
      }
      console.log("save1");
      var f = clearexam(rev.questionnaireid, res);
      //console.log("save1");
      console.log("f: ", f);
      if (f == 0) return;
      //console.log("f: ", f);
      var ques = rev.questions;
      //console.log("save2");
      var y = addquestion(ques, rev.questionnaireid, res);
      //console.log("save2");
      console.log("y: ", y);
      if (y == 0) return;
      //console.log("y: ", y);
      // getquestionid(rev.questionnaireid,rev.questions,res);
      res.json(json);
    })
  }

  this.releaseques = function (id, res) {
    //console.log("in release sql");
    var sql = "SELECT * FROM questionnaire WHERE questionnaireid = ?";
    var para = [id];
    var json = getJsonFile('./release.json5');
    connection.query(sql, para, function (err, result) {
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json)
        return;
      }
      if (result[0]) {
        //更新发布时间
        var d = new Date();
        var sqlu = "update questionnaire set releasetime = ? where questionnaireid = ?";
        var parau = [d, id]
        connection.query(sqlu, parau);
        // TODO 部署时修改此处！
        json.data.link = hostaddr + "/fillin?questionnaireid=" + id.toString();
      } else {
        json.data.link = "err";
        json.meta.status = 400;
      }
      res.json(json);
      return;
    })
  }
  this.editques = function (id, res) {
    var json = getJsonFile('./edit.json5');
    var sql = "SELECT * FROM questionnaire WHERE questionnaireid = ?"
    var para = [id]
    connection.query(sql, para, function (err, result) {
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json)
        return;
      }
      if (result[0]) {
        //console.log("in editques result: ", result[0]);
        json.data.questionnaireid = id;
        json.data.title = result[0].title;
        json.data.introduction = result[0].introduction;
        var time = new Date(result[0].endtime)
        json.data.endtime = time.getTime() / 1000;
        //清空模板中题目
        json.data.questions = [];
        //查询问卷中所有题目
        sql = "SELECT * FROM question Q LEFT JOIN options O ON Q.questionID=O.questionID WHERE Q.questionnaireid=?"
        para = [id]
        connection.query(sql, para, function (err, result) {
          if (err) {
            console.log(err);
            json.meta.status = 400;
            res.json(json)
            return;
          }
          if (result[0]) {
            //console.log(result)
            for (var i = 0; i < result.length; ++i) {
              var qid = result[i].questionID
              var q = { title: '', topic: '', selects: [] }
              q.title = result[i].Title
              q.topic = result[i].topic
              if (q.topic != 3)
                q.selects = result[i].selects.split('|')
              console.log("question:", q)
              json.data.questions.push(q)
            }
            res.json(json)
            return;
          } else {
            console.log("no result")
            json.data.questionnaireid = "err";
            json.meta.status = 400;
            res.json(json)
            return;
          }
        })
      } else {//未找到
        console.log("no result")
        json.data.questionnaireid = "err";
        json.meta.status = 400;
        res.json(json)
        return;
      }
    })
  }

  this.submitexam = function (rev, res) {
    console.log("submit");
    var sql1 = "select questionID from question where questionnaireid=?"
    var para1 = [rev.questionnaireid];
    connection.query(sql1, para1, function (err, result) {
      var json = getJsonFile('./save.json5');
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json);
        return;
      }
      console.log(result);
      for (let i = 0; i < result.length; ++i) {
        var sql2 = "insert into answer(questionID, answers) values(?,?)"
        var para2 = [result[i].questionID, rev.answers[i]];
        console.log(para2);
        connection.query(sql2, para2, function (err, result) {
          var json1 = getJsonFile('./save.json5');
          if (err) {
            console.log(err);
            json1.meta.status = 400;
            res.json(json1);
            return;
          }
        })
      }
      res.json(json);
      return;

    })
  }

  function findQues(arr, id) {
    for (var i = 0; i < arr.length; ++i) {
      if (arr[i].id == id) {
        return i
      }
    }
    return -1
  }

  this.getQuesData = function (qid, res) {
    console.log("get questionnaire data: ", qid)
    var json = getJsonFile('./data.json5')
    var sql1 = "select * from questionnaire where questionnaireid=?"
    var para1 = [qid];
    connection.query(sql1, para1, function (err, result) {
      if (err) {
        console.log(err);
        json.meta.status = 400;
        res.json(json);
        return;
      }
      if (result[0]) {
        json.data.questionnaireid = qid
        json.data.questionnaireTitle = result[0].title
        json.data.questionnaireData = []
        var sql2 = "SELECT q.questionID,Title,topic,selects,answers FROM (question q, answer a) left join options o ON q.questionID=o.questionID WHERE q.questionID=a.questionID AND q.questionnaireid = ?"
        var para2 = [qid]
        connection.query(sql2, para2, function (err, result) {
          if (err) {
            console.log(err)
            json.meta.status = 400;
            res.json(json);
            return;
          }
          if (result[0]) {
            alldata = []
            //遍历处理所有结果
            for (var ritem of result) {
              var quesid = ritem.questionID
              var questitle = ritem.Title
              var questopic = ritem.topic
              if (findQues(alldata, quesid) == -1) {
                //首次遇到问题
                console.log('first meet: ', quesid)
                //console.log('alldata: ', alldata)
                var ditem = { id: '', title: '', topic: 0, totalnum: 0, qdata: [] }
                ditem.id = quesid
                ditem.title = questitle
                ditem.topic = questopic
                if (questopic < 3 && ritem.selects != null) {
                  //非填空题，添加所有选项
                  var quesselects = ritem.selects.split('|')
                  for (select of quesselects) {
                    var dselect = { value: 0, name: select }
                    ditem.qdata.push(dselect)
                  }
                }
                alldata.push(ditem)
              }
              //处理answers
              var pos = findQues(alldata, quesid)
              alldata[pos].totalnum += 1
              if (questopic == 3) {
                //填空题
                var quesanswer = ritem.answers
                alldata[pos].qdata.push({ value: '1', name: quesanswer })
              } else {
                //非填空题
                var quesanswers = ritem.answers.split(',')
                for (var i = 0; i < quesanswers.length; ++i) {
                  var ans = parseInt(quesanswers[i])
                  alldata[pos].qdata[ans].value++
                }
              }

            }
            //处理结果格式
            for (d of alldata) {
              var finalitem = {
                message: { questionTitle: d.title, topic: d.topic, totalnum: d.totalnum },
                qdata: d.qdata
              }
              json.data.questionnaireData.push(finalitem)
            }
            res.json(json)
          } else {
            console.log('no answer yet')
            res.json(json)
          }
        })
      } else {
        console.log("no result");
        json.meta.status = 400;
        res.json(json);
        return;
      }
    })
  }
  this.recover = function (id, res) {
    var sql = "update questionnaire set CH='0' where questionnaireid = ?";
    var modSqlParams = [id];
    connection.query(sql, modSqlParams, function (err, result) {
      if (err) {
        console.log(err);
        var json = getJsonFile('./deleteQuestionnaire.json5');
        json.meta.msg = '恢复失败';
        res.json(json);
        return;
      }
    })
    var json1 = getJsonFile('./deleteQuestionnaire.json5');
    json1.meta.msg = '恢复成功';
    res.json(json1);
  }
}
module.exports = Mysql
